﻿using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using KeRui.Common;
using System.Text.RegularExpressions;

public partial class Manage_Database_Query : System.Web.UI.Page
{
    
    public string SqlStr = PageRequest.GetString("sqlstr").Replace("\r\n"," ").Replace("  "," ");
    public DataSet ds = null;

    protected void Page_Load(object sender, EventArgs e)
    {
        ManageHelper.CheckAdminLogin();
        ManageHelper.CheckAdminPower("system_databasequery");
        if (ManageHelper.PageAct() == "query")
        {
            ds = dsResult(SqlStr);
            if (ds == null)
                Response.Redirect("query.aspx?sqlstr=" + Utils.UrlEncode(SqlStr) + "&errmsg=" + Utils.RemoveHtmlContent("“" + SqlStr + "”不是有效的数据库查询字符串！"));
            //else
            //    Response.Redirect("query.aspx?sqlstr=" + Utils.UrlEncode(SqlStr) + "&sucmsg=查询成功，共返回<strong>" + ds.Tables[0].Rows.Count.ToString() + "</strong>条结果。");
        }

    }

    private DataSet dsResult(string sql)
    {
        string strField = "";
        string strTable = "";
        string strWhere = "";
        string strOrder = "";

        string P1 = @"select(?<strField>(.*))from(?<strTable>(.*))where(?<strWhere>(.*))order by(?<strOrder>(.*))";
        string P2 = @"select(?<strField>(.*))from(?<strTable>(.*))where(?<strWhere>(.*))";
        string P3 = @"select(?<strField>(.*))from(?<strTable>(.*))";

        if (strField == "")
        {
            MatchCollection matches = Regex.Matches(sql, P2, RegexOptions.IgnoreCase);
            foreach (Match match in matches)
            {
                GroupCollection Items = match.Groups;
                strField = Items["strField"].Value.Trim();
                strTable = Items["strTable"].Value.Trim();
                strWhere = Items["strWhere"].Value.Trim();
                strOrder = Items["strOrder"].Value.Trim();
            }
        }

        if (strField == "")
        {
            MatchCollection matches = Regex.Matches(sql, P2, RegexOptions.IgnoreCase);
            foreach (Match match in matches)
            {
                GroupCollection Items = match.Groups;
                strField = Items["strField"].Value.Trim();
                strTable = Items["strTable"].Value.Trim();
                strWhere = Items["strWhere"].Value.Trim();
                strOrder = Items["strOrder"].Value.Trim();
            }
        }

        if (strField == "")
        {
            MatchCollection matches = Regex.Matches(sql, P3, RegexOptions.IgnoreCase);
            foreach (Match match in matches)
            {
                GroupCollection Items = match.Groups;
                strField = Items["strField"].Value.Trim();
                strTable = Items["strTable"].Value.Trim();
                strWhere = Items["strWhere"].Value.Trim();
                strOrder = Items["strOrder"].Value.Trim();
            }
        }

        if (strField == "")
            return null;

        if (!SqlIsSafe(strField) || !SqlIsSafe(strTable) || !SqlIsSafe(strWhere) || !SqlIsSafe(strOrder))
            return null;

        KeRui.DAL.Common db = new KeRui.DAL.Common();
        DataSet ds;
        try
        {
            ds=db.GetSelect(strTable, strWhere, strOrder, strField);
        }
        catch(Exception ex)
        {
            return null;
        }
        return ds;
    }

    ///  <summary> 
    ///  SQL是否安全
    ///  </summary> 
    ///  <param name="Str">Sql字符串</param> 
    ///  <returns></returns> 
    private bool SqlIsSafe(string Str)
    {
        bool ReturnValue = true;
        try
        {
            if (Str.Trim() != "")
            {
                string SqlStr = ";¦exec¦insert¦select¦delete¦update¦truncate¦drop¦alter¦char¦declare¦master¦xp_cmdshell¦localgroup";

                string[] anySqlStr = SqlStr.Split('¦');
                foreach (string ss in anySqlStr)
                {
                    if (Str.ToLower().IndexOf(ss.Trim()) >= 0)
                    {
                        ReturnValue = false;
                        break;
                    }
                }
            }
        }
        catch
        {
            ReturnValue = false;
        }
        return ReturnValue;
    } 
}
